package com.sogoodsoft.service.exp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 导入oralce权限表到MySQL表
 * @author xiaozu
 *
 */
public class Export extends DBHelper {


	public static void test() {
		try {
			String sql = "select name from acct_user ";
			oraState = ora.createStatement();
			oraRS = oraState.executeQuery(sql);
			while(oraRS.next()){
				logger.debug(oraRS.getString(1));
			}
			oraRS.close();
			ora.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void expAuthority(){
		connect();
		try {
			mysql.setAutoCommit(false);
			mysqlState = mysql.createStatement();
			String querySql = "select name,cate from acct_authority order by id asc";
			StringBuffer buffer;
			oraState = ora.createStatement();
			oraRS = oraState.executeQuery(querySql);
			while(oraRS.next()){
				buffer = new StringBuffer();
				buffer.append("insert into acct_authority");
				buffer.append("(");
				buffer.append("name,");
				buffer.append("cate");
				buffer.append(")");
				buffer.append(" values('");
				buffer.append(oraRS.getString(1));
				buffer.append("','");
				buffer.append(oraRS.getString(2));
				buffer.append("')");
				logger.debug(buffer.toString());
				mysqlState.addBatch(buffer.toString());
			}
			oraRS.close();
			oraState.close();
			mysqlState.executeBatch();
			mysql.commit();
			mysqlState.close();
			
			ora.close();
			mysql.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void exportRole(){
		connect();
		try {
			mysql.setAutoCommit(false);
			mysqlState = mysql.createStatement();
			String querySql = "select name,odr from acct_role order by id asc";
			StringBuffer buffer;
			oraState = ora.createStatement();
			oraRS = oraState.executeQuery(querySql);
			while(oraRS.next()){
				buffer = new StringBuffer();
				buffer.append("insert into acct_role");
				buffer.append("(");
				buffer.append("name,");
				buffer.append("odr");
				buffer.append(")");
				buffer.append(" values('");
				buffer.append(oraRS.getString(1));
				buffer.append("','");
				buffer.append(oraRS.getInt(2));
				buffer.append("')");
				logger.debug(buffer.toString());
				mysqlState.addBatch(buffer.toString());
			}
			oraRS.close();
			oraState.close();
			mysqlState.executeBatch();
			mysql.commit();
			mysqlState.close();
			
			ora.close();
			mysql.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void exportSysClass(){
		connect();
		int oid=0;
		int mid=0;
		try {
			mysqlState = mysql.createStatement();
			String querySql = "select * from sys_class where rownum<2 order by id asc";
			oraState = ora.createStatement();
			oraRS = oraState.executeQuery(querySql);
			
			mysqlState = mysql.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			mysqlRS = mysqlState.executeQuery("SELECT id,name,code,value,odr,status,type,note,description FROM sys_class ORDER BY id LIMIT 1,1");
		
			if(oraRS.next()){
				mysqlRS.moveToInsertRow();
				mysqlRS.updateString("name", oraRS.getString("name"));
				mysqlRS.updateString("code", oraRS.getString("code"));
				mysqlRS.updateString("value", oraRS.getString("value"));
				mysqlRS.updateInt("odr", oraRS.getInt("odr"));
				mysqlRS.updateString("status",oraRS.getString("status"));
				//mysqlRS.updateInt("parent_id", oraRS.getInt("id"));
				mysqlRS.updateString("type",oraRS.getString("type"));
				mysqlRS.updateString("note",oraRS.getString("note"));
				mysqlRS.updateString("description",oraRS.getString("description"));
				mysqlRS.insertRow();
				Statement idstate = mysql.createStatement();
				ResultSet idRS = idstate.executeQuery("select max(id) from sys_class ");
				if(idRS.next()){
				mid = idRS.getInt(1);
				}
				idRS.close();
				idstate.close();
				oid = oraRS.getInt("id");
				compositeSysClass(oid,mid);
				logger.debug("insert "+oid);
			}
			oraRS.close();
			oraState.close();
			
			mysqlState.close();
			oraState.close();
			
			ora.close();
			mysql.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void compositeSysClass(int oraid,int mysqlid){
		String sql = "select * from sys_class where parent_id = "+oraid;	
		String msql = "SELECT id,name,code,value,odr,status,parent_id,type,note,description FROM sys_class ORDER BY id LIMIT 1,1 ";
		int oid = oraid;
		int mid = mysqlid;
		try {
			Statement os = ora.createStatement();
			ResultSet rs = os.executeQuery(sql);
			
			Statement ms = mysql.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet mrs = ms.executeQuery(msql);
			
			while(rs.next()){
				mrs.moveToInsertRow();
				mrs.updateString("name", rs.getString("name"));
				mrs.updateString("code", rs.getString("code"));
				mrs.updateString("value", rs.getString("value"));
				mrs.updateInt("odr", rs.getInt("odr"));
				mrs.updateString("status",rs.getString("status"));
				mrs.updateInt("parent_id", mysqlid);
				mrs.updateString("type",rs.getString("type"));
				mrs.updateString("note",rs.getString("note"));
				mrs.updateString("description",rs.getString("description"));
				mrs.insertRow();
				oid = rs.getInt("id");
				Statement idstate = mysql.createStatement();
				ResultSet idRS = idstate.executeQuery("select max(id) from sys_class ");
				if(idRS.next()){
				mid = idRS.getInt(1);
				}
				idRS.close();
				idstate.close();
				logger.debug("insert  mid = "+mid);
				compositeSysClass(oid,mid);
			}
			rs.close();
			mrs.close();
			
			os.close();
			ms.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	public static void exportOrg(){
		connect();
		int oid=0;
		int mid=0;
		try {
			mysqlState = mysql.createStatement();
			String querySql = "select * from organization where rownum<2 order by id asc";
			oraState = ora.createStatement();
			oraRS = oraState.executeQuery(querySql);
			
			mysqlState = mysql.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			mysqlRS = mysqlState.executeQuery("SELECT id,parent_id,node_name,code,authority,status,odr,type,nick_name,note,description FROM organization ORDER BY id LIMIT 1,1");
		
			if(oraRS.next()){
				mysqlRS.moveToInsertRow();
				mysqlRS.updateString("node_name", oraRS.getString("node_name"));
				mysqlRS.updateString("code", oraRS.getString("code"));
				mysqlRS.updateString("authority", oraRS.getString("authority"));
				mysqlRS.updateInt("odr", oraRS.getInt("odr"));
				mysqlRS.updateString("status",oraRS.getString("status"));
				//mysqlRS.updateInt("parent_id", oraRS.getInt("id"));
				mysqlRS.updateString("type",oraRS.getString("type"));
				mysqlRS.updateString("note",oraRS.getString("note"));
				mysqlRS.updateString("description",oraRS.getString("description"));
				mysqlRS.updateString("nick_name",oraRS.getString("nick_name"));
				mysqlRS.insertRow();
				Statement idstate = mysql.createStatement();
				ResultSet idRS = idstate.executeQuery("select max(id) from organization");
				if(idRS.next()){
				mid = idRS.getInt(1);
				}
				idRS.close();
				idstate.close();
				oid = oraRS.getInt("id");
				compositeOrg(oid,mid);
				logger.debug("insert "+oid);
			}
			oraRS.close();
			oraState.close();
			
			mysqlState.close();
			oraState.close();
			
			ora.close();
			mysql.close();
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public static void compositeOrg(int oraid,int mysqlid){
		String sql = "select * from organization where parent_id = "+oraid;	
		String msql = "SELECT id,parent_id,node_name,code,authority,status,odr,type,nick_name,note,description FROM organization ORDER BY id LIMIT 1,1 ";
		int oid = oraid;
		int mid = mysqlid;
		try {
			Statement os = ora.createStatement();
			ResultSet rs = os.executeQuery(sql);
			
			Statement ms = mysql.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			ResultSet mrs = ms.executeQuery(msql);
			
			while(rs.next()){
				mrs.moveToInsertRow();
				mrs.updateString("node_name", rs.getString("node_name"));
				mrs.updateString("code", rs.getString("code"));
				mrs.updateString("authority", rs.getString("authority"));
				mrs.updateInt("odr", rs.getInt("odr"));
				mrs.updateString("status",oraRS.getString("status"));
				mrs.updateInt("parent_id", mysqlid);
				mrs.updateString("type",rs.getString("type"));
				mrs.updateString("note",rs.getString("note"));
				mrs.updateString("description",rs.getString("description"));
				mrs.updateString("nick_name",rs.getString("nick_name"));
				mrs.insertRow();
				oid = rs.getInt("id");
				Statement idstate = mysql.createStatement();
				ResultSet idRS = idstate.executeQuery("select max(id) from organization");
				if(idRS.next()){
				mid = idRS.getInt(1);
				}
				idRS.close();
				idstate.close();
				logger.debug("insert  mid = "+mid);
				compositeOrg(oid,mid);
			}
			rs.close();
			mrs.close();
			
			os.close();
			ms.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
			//connect();
			//test();
		//expAuthority();
		//exportRole();
		exportSysClass();
		//exportOrg();
	}

}
